package com.mstanford.movie.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.mstanford.movie.utils.DBUtil;
import com.mstanford.movie.utils.DataUtil;

public class MovieDao {

	private Connection con;
	
	private PreparedStatement ps;
	
	private ResultSet rs;
	
	/**
	 * 查询单个成员
	 * @param map
	 */
	public Map<String, String> querySingleMember(Map<String, String> map) {
		
		String memberEmail = map.get("memberEmail");
		String memberPwd = map.get("memberPwd");
		
		String sql = "select * from member where member_email=? and member_pwd=?";
		
		try {
			con = DBUtil.getConnection();
			
			ps = con.prepareStatement(sql);
			ps.setString(1, memberEmail);
			ps.setString(2, memberPwd);
			
			rs = ps.executeQuery();
			
			if(rs.next()) {
				String memberId = rs.getString("member_id");
				String memberName = rs.getString("member_name");
				String memberPhone = rs.getString("member_phone");
				String memberMoney = rs.getString("member_money");
				String memberGender = rs.getString("member_gender");
				String memberPhoto = rs.getString("member_photo");
				
				map.put("memberId", memberId);
				map.put("memberName", memberName);
				map.put("memberPhone", memberPhone);
				map.put("memberMoney", memberMoney);
				map.put("memberGender", memberGender);
				map.put("memberPhoto", memberPhoto);
				map.put("hasMember", "true"); // 有用户记录
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
		return map;
	}
	
	/**
	 * 查询是否有该邮箱的成员
	 * @param map
	 */
	public void queryHasMember(Map<String, String> map) {
		
		String memberEmail = map.get("memberEmail");
		
		String sql = "select count(1) from member where member_email=?";
		
		try {
			con = DBUtil.getConnection();
			
			ps = con.prepareStatement(sql);
			ps.setString(1, memberEmail);
			
			rs = ps.executeQuery();
			
			if(rs.next()) {
				int num = rs.getInt(1);
				
				if(num != 0) {
					map.put("hasMember", "true");
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
	}
	
	/**
	 * 根据成员ID查询成员余额
	 * @param map
	 */
	public void queryMemberMoneyByMemberId(Map<String, String> map) {
		
		String memberId = map.get("memberId");
		
		String sql = "select member_money from member where member_id=?";
		
		try {
			con = DBUtil.getConnection();
			
			ps = con.prepareStatement(sql);
			ps.setString(1, memberId);
			
			rs = ps.executeQuery();
			
			if(rs.next()) {
				float memberMoney = rs.getFloat(1);
				map.put("memberMoney", memberMoney + "");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
	}
	
	/**
	 * 根据成员ID更新成员余额
	 * @param map
	 */
	public void updateMemberMoneyByMemberId(Map<String, String> map) {
		
		String memberId = map.get("memberId");
		String memberMoney = map.get("memberMoney");
		
		String sql = "update member set member_money=? where member_id=?";
		
		try {
			con = DBUtil.getConnection();
			
			ps = con.prepareStatement(sql);
			ps.setString(1, memberMoney);
			ps.setString(2, memberId);
			
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
	}
	
	/**
	 * 新增成员
	 * @param map
	 */
	public int addMember(Map<String, String> map) {
		
		String memberEmail = map.get("memberEmail");
		String memberPwd = map.get("memberPwd");
		String memberName = map.get("memberName");
		String memberPhone = map.get("memberPhone");
		String memberMoney = map.get("memberMoney");
		String memberGender = map.get("memberGender");
		String memberPhoto = map.get("memberPhoto");
		
		String sql = "insert into member values(?,?,?,?,?,?,?)";
		int num=0;
		try {
			con = DBUtil.getConnection();
			
			ps = con.prepareStatement(sql);
			ps.setString(1, memberEmail);
			ps.setString(2, memberPwd);
			ps.setString(3, memberName);
			ps.setString(4, memberPhone);
			ps.setString(5, memberMoney);
			ps.setString(6, memberGender);
			ps.setString(7, memberPhoto);
			
			num = ps.executeUpdate();
			
			if(num != 0) {
				map.put("add", "true");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
		return num;
	}
	
	/**
	 * 根据上映日期统计电影记录
	 * @param map
	 */
	public void queryMovieByDateCount(Map<String, String> map) {
		
		String movieName = map.get("movieName");
		String type = map.get("type");
		String date = map.get("date");
		
		String sql = "select count(1) from movie where movie_date<=?"; // 正在热播
		
		if(!"hotPlay".equals(type)) {
			sql = "select count(1) from movie where movie_date>?"; // 即将上映
		}
		
		if(!DataUtil.isEmpty(movieName)) {
			sql += " and movie_name like '%" + movieName + "%'";
		}
		
		try {
			con = DBUtil.getConnection();
			
			ps = con.prepareStatement(sql);
			ps.setString(1, date);
			
			rs = ps.executeQuery();
			
			if(rs.next()) {
				map.put("dataCount", rs.getInt(1) + "");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
	}
	
	/**
	 * 根据上映日期查询电影记录
	 * @param condMap
	 * @return
	 */
	public List<Map<String, String>> queryMovieByDate(Map<String, String> condMap) {
		
		String _movieName = condMap.get("movieName");
		String type = condMap.get("type");
		String date = condMap.get("date");
		int limit = Integer.parseInt(condMap.get("limit"));
		int pos = Integer.parseInt(condMap.get("pos"));
		
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		
		String sql = "select * from movie where movie_date<=?"; // 正在热播
		
		if(!"hotPlay".equals(type)) {
			sql = "select * from movie where movie_date>?"; // 即将上映
		}
		
		if(!DataUtil.isEmpty(_movieName)) {
			sql += " and movie_name like '%" + _movieName + "%'";
		}
		
		sql = DataUtil.getPagingSqlBySQLServer2005High(sql, limit, pos);
		
		try {
			con = DBUtil.getConnection();

			ps = con.prepareStatement(sql);
			ps.setString(1, date);
			
			rs = ps.executeQuery();
			
			while(rs.next()) {
				String movieId = rs.getString("movie_id");
				String movieName = rs.getString("movie_name");
				String movieDirector = rs.getString("movie_director");
				String movieActor = rs.getString("movie_actor");
				String movieInfo = rs.getString("movie_info");
				String movieLanguage = rs.getString("movie_language");
				String movieKind = rs.getString("movie_kind");
				String moviePhoto = rs.getString("movie_photo");
				String movieLong = rs.getString("movie_long");
				String movieDate = rs.getString("movie_date");
				String movieEdition = rs.getString("movie_edition");
				
				Map<String, String> map = new HashMap<String, String>();
				map.put("movieId", movieId);
				map.put("movieName", movieName);
				map.put("movieDirector", movieDirector);
				map.put("movieActor", movieActor);
				map.put("movieInfo", movieInfo);
				map.put("movieLanguage", movieLanguage);
				map.put("movieKind", movieKind);
				map.put("moviePhoto", moviePhoto);
				map.put("movieLong", movieLong);
				map.put("movieDate", movieDate);
				map.put("movieEdition", movieEdition);
				
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
		
		return list;
	}
	
	/**
	 * 根据电影ID查询单个记录
	 * @param movieId
	 * @return
	 */
	public Map<String, String> querySingleMovieDetails(String movieId) {
		
		Map<String, String> map = new HashMap<String, String>();
		
		StringBuffer sql = new StringBuffer();
		sql.append("select t1.*,t2.edition_name,t3.kind_name,t4.language_name ")
		   .append("from movie t1,edition t2,kind t3,language t4 ")
		   .append("where t1.movie_language=t4.language_id and t1.movie_kind=t3.kind_id and ")
		   .append("t1.movie_edition=t2.edition_id and t1.movie_id=?");
		
		try {
			con = DBUtil.getConnection();
			
			ps = con.prepareStatement(sql.toString());
			ps.setString(1, movieId);
			
			rs = ps.executeQuery();
			
			if(rs.next()) {
				String movieName = rs.getString("movie_name");
				String movieDirector = rs.getString("movie_director");
				String movieActor = rs.getString("movie_actor");
				String movieInfo = rs.getString("movie_info");
				String movieLanguage = rs.getString("movie_language");
				String movieKind = rs.getString("movie_kind");
				String moviePhoto = rs.getString("movie_photo");
				String movieLong = rs.getString("movie_long");
				String movieDate = rs.getString("movie_date").substring(0, 10);
				String[] arr = movieDate.split("-");
				movieDate = arr[0] + "年" + arr[1] + "月" + arr[2] + "日";
				String movieEdition = rs.getString("movie_edition");
				String editionName = rs.getString("edition_name");
				String kindName = rs.getString("kind_name");
				String languageName = rs.getString("language_name");
				
				map.put("movieId", movieId);
				map.put("movieName", movieName);
				map.put("movieDirector", movieDirector);
				map.put("movieActor", movieActor);
				map.put("movieInfo", movieInfo);
				map.put("movieLanguage", movieLanguage);
				map.put("movieKind", movieKind);
				map.put("moviePhoto", moviePhoto);
				map.put("movieLong", movieLong);
				map.put("movieDate", movieDate);
				map.put("movieEdition", movieEdition);
				map.put("editionName", editionName);
				map.put("kindName", kindName);
				map.put("languageName", languageName);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
		
		return map;
	}
	
	/**
	 * 根据条件查询排片记录
	 * @param condMap
	 * @return
	 */
	public List<Map<String, String>> queryPlayByCond(Map<String, String> condMap) {
		
		String pattern = "yyyy-MM-dd HH:mm";
		String movieName = condMap.get("movieName");
		String date = condMap.get("date");
		String edition = condMap.get("edition");
		String time = condMap.get("time");
		
		StringBuffer sql = new StringBuffer();
		sql.append("select * from(select t5.*,t6.play_id,convert(varchar(10),t6.play_time,20) play_time1,")
		   .append("convert(varchar(5),t6.play_time,8) play_time2,t6.play_price from (select t1.*,")
		   .append("t2.edition_name,t3.kind_name,t4.language_name from movie t1,edition t2,kind t3,")
		   .append("language t4 where t1.movie_language=t4.language_id and t1.movie_kind=t3.kind_id and ")
		   .append("t1.movie_edition=t2.edition_id and t1.movie_name=?) t5,play t6 where t5.movie_id=t6.play_movie) t7 ")
		   .append("where movie_edition in (").append(edition).append(") and play_time1 in (").append(date).append(")");

		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		
		try {
			con = DBUtil.getConnection();
			
			ps = con.prepareStatement(sql.toString());
			ps.setString(1, movieName);
			
			rs = ps.executeQuery();

			while(rs.next()) {
				String playTime1 = rs.getString("play_time1");
				String playTime2 = rs.getString("play_time2");
				String playId = rs.getString("play_id");
				String movieId = rs.getString("movie_id");
				String movieLanguage = rs.getString("movie_language");
				String languageName = rs.getString("language_name");
				String movieEdition = rs.getString("movie_edition");
				String editionName = rs.getString("edition_name");
				String playPrice = rs.getString("play_price");
				
				String playTime = playTime1 + " " + playTime2;
				
				if("ap".equals(time)) { // 早中(排除晚上)
					if(DataUtil.compareDate(playTime, playTime1 + " 18:00", pattern) == 1 && 
							DataUtil.compareDate(playTime, playTime1 + " 23:59", pattern) != 1) {
						continue;
					}
				} else if("an".equals(time)) { // 早晚(排除下午)
					if(DataUtil.compareDate(playTime, playTime1 + " 12:00", pattern) == 1 && 
							DataUtil.compareDate(playTime, playTime1 + " 18:00", pattern) != 1) {
						continue;
					}
				} else if("pn".equals(time)) { // 中晚(排除上午)
					if(DataUtil.compareDate(playTime, playTime1 + " 00:00", pattern) != -1 && 
							DataUtil.compareDate(playTime, playTime1 + " 12:00", pattern) != 1) {
						continue;
					}
				} else if("am".equals(time)) { // 早(排除下午和晚上)
					if((DataUtil.compareDate(playTime, playTime1 + " 12:00", pattern) == 1 && 
							DataUtil.compareDate(playTime, playTime1 + " 18:00", pattern) != 1) || 
							(DataUtil.compareDate(playTime, playTime1 + " 18:00", pattern) == 1 && 
									DataUtil.compareDate(playTime, playTime1 + " 23:59", pattern) != 1)) {
						continue;
					}
				} else if("pm".equals(time)) { // 中(排除上午和晚上)
					if((DataUtil.compareDate(playTime, playTime1 + " 00:00", pattern) != -1 && 
							DataUtil.compareDate(playTime, playTime1 + " 12:00", pattern) != 1) || 
							(DataUtil.compareDate(playTime, playTime1 + " 18:00", pattern) == 1 && 
									DataUtil.compareDate(playTime, playTime1 + " 23:59", pattern) != 1)) {
						continue;
					}
				} else if("night".equals(time)) { // 晚(排除上午和下午)
					if((DataUtil.compareDate(playTime, playTime1 + " 00:00", pattern) != -1 && 
							DataUtil.compareDate(playTime, playTime1 + " 12:00", pattern) != 1) || 
							(DataUtil.compareDate(playTime, playTime1 + " 12:00", pattern) == 1 && 
									DataUtil.compareDate(playTime, playTime1 + " 18:00", pattern) != 1)) {
						continue;
					}
				}
				
				Map<String, String> map = new HashMap<String, String>();
				map.put("playTime1", playTime1);
				map.put("playTime2", playTime2);
				map.put("playId", playId);
				map.put("movieId", movieId);
				map.put("movieName", movieName);
				map.put("movieLanguage", movieLanguage);
				map.put("languageName", languageName);
				map.put("movieEdition", movieEdition);
				map.put("editionName", editionName);
				map.put("playPrice", playPrice + "元");

				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
		
		return list;
	}
	
	/**
	 * 根据排片ID统计已卖票数
	 * @return
	 */
	public int queryTicketCountByPlayId(String playId) {
		
		String sql = "select count(1) from ticket where ticket_flag=1 and ticket_play=?";
		
		try {
			con = DBUtil.getConnection();
			
			ps = con.prepareStatement(sql);
			ps.setString(1, playId);
			
			rs = ps.executeQuery();
			
			if(rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
		
		return 0;
	}
	
	/**
	 * 根据排片ID查询电影信息
	 * @param playId
	 * @return
	 */
	public Map<String, String> queryMovieByPlayId(String playId) {
		
		Map<String, String> map = new HashMap<String, String>();
		
		StringBuffer sql = new StringBuffer();
		sql.append("select convert(varchar(10),t1.play_time,20) play_time1,")
		   .append("convert(varchar(5),t1.play_time,8) play_time2,t1.play_price,")
		   .append("t2.movie_name,t3.edition_name from play t1,movie t2,edition t3 ")
		   .append("where t1.play_movie=t2.movie_id and t2.movie_edition=t3.edition_id and t1.play_id=?");
		
		try {
			con = DBUtil.getConnection();
			
			ps = con.prepareStatement(sql.toString());
			ps.setString(1, playId);
			
			rs = ps.executeQuery();
			
			if(rs.next()) {
				String playTime1 = rs.getString("play_time1");
				String playTime2 = rs.getString("play_time2");
				String playPrice = rs.getString("play_price");
				String movieName = rs.getString("movie_name");
				String editionName = rs.getString("edition_name");
				
				String[] timeArr = playTime1.split("-");
				String playTime3 = timeArr[1] + "月" + timeArr[2] + "日";
				
				map.put("playTime1", playTime1);
				map.put("playTime2", playTime2);
				map.put("playTime3", playTime3);
				map.put("playPrice", playPrice);
				map.put("movieName", movieName);
				map.put("editionName", editionName);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
		
		return map;
	}
	
	/**
	 * 根据排片ID查询座位
	 * @param playId
	 * @return
	 */
	public List<String> querySeatByPlayId(String playId) {
		
		List<String> list = new ArrayList<String>();
		
		String sql = "select ticket_seat from ticket where ticket_flag=1 and ticket_play=?";
		
		try {
			con = DBUtil.getConnection();
			
			ps = con.prepareStatement(sql);
			ps.setString(1, playId);
			
			rs = ps.executeQuery();
			
			while(rs.next()) {
				list.add(rs.getString("ticket_seat"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
		
		return list;
	}
	
	/**
	 * 查询所有排片记录
	 * @param condMap
	 * @return
	 */
	public List<Map<String, String>> queryPlayTime(Map<String, String> condMap) {
		
		String date = condMap.get("date");
		String edition = condMap.get("edition");
		String play = condMap.get("play");
		
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		
		StringBuffer sql = new StringBuffer();
		sql.append("select * from (select t1.play_id,t1.play_movie,convert(varchar(10),t1.play_time,20) play_time1,")
		   .append("convert(varchar(5),t1.play_time,8) play_time2,t1.play_price,t2.movie_name,t2.movie_long,")
		   .append("t2.movie_edition,t3.language_name,t4.kind_name,t5.edition_name,")
		   .append("(select count(1) from ticket where ticket_flag=1 and ticket_play=play_id) ticket_num ")
		   .append("from play t1,movie t2,language t3,kind t4,edition t5 ")
		   .append("where t1.play_movie=t2.movie_id and t2.movie_language=t3.language_id ")
		   .append("and t2.movie_kind=t4.kind_id and t2.movie_edition=t5.edition_id) t ")
		   .append("where play_time1=? ");
		
		if(!DataUtil.isEmpty(edition)) {
			sql.append("and movie_edition in (").append(edition).append(") ");
		}
		
		if(!DataUtil.isEmpty(play)) {
			sql.append("and play_id in (").append(play).append(") ");
		}
		
		sql.append("order by movie_name");
		
		try {
			con = DBUtil.getConnection();
			
			ps = con.prepareStatement(sql.toString());
			ps.setString(1, date);
			
			rs = ps.executeQuery();
			
			while(rs.next()) {
				String playId = rs.getString("play_id");
				String movieId = rs.getString("play_movie");
				String playTime1 = rs.getString("play_time1");
				String playTime2 = rs.getString("play_time2");
				String playPrice = rs.getString("play_price");
				String movieName = rs.getString("movie_name");
				String movieLong = rs.getString("movie_long");
				String languageName = rs.getString("language_name");
				String kindName = rs.getString("kind_name");
				String editionName = rs.getString("edition_name");
				String ticketNum = rs.getString("ticket_num");
				
				Map<String, String> map = new HashMap<String, String>();
				map.put("playId", playId);
				map.put("movieId", movieId);
				map.put("playTime1", playTime1);
				map.put("playTime2", playTime2);
				map.put("playPrice", playPrice);
				map.put("movieName", movieName);
				map.put("movieLong", movieLong);
				map.put("languageName", languageName);
				map.put("kindName", kindName);
				map.put("editionName", editionName);
				map.put("ticketNum", ticketNum);
				
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
		
		return list;
	}
	
	/**
	 * 添加电影票
	 * @param map
	 */
	public void addTicket(Map<String, String> map) {
		
		String ticketPlay = map.get("playId");
		String ticketMember = map.get("memberId");
		String[] ticketSeatArr = map.get("ticketSeat").split(",");
		String ticketPrice = map.get("ticketPrice");
		String ticketFlag = map.get("ticketFlag");
		
		String sql = "insert into ticket(ticket_play,ticket_member,ticket_code,ticket_seat,ticket_price,ticket_flag) values(?,?,?,?,?,?)";
		
		try {
			con = DBUtil.getConnection();
			// 获取当前自动提交模式
			boolean autoCommit = con.getAutoCommit();
			// 取消自动提交事务
			con.setAutoCommit(false);
			
			ps = con.prepareStatement(sql);
			
			for(String ticketSeat : ticketSeatArr) {
				String ticketCode = DataUtil.createUUID();
				
				ps.setString(1, ticketPlay);
				ps.setString(2, ticketMember);
				ps.setString(3, ticketCode);
				ps.setString(4, ticketSeat);
				ps.setString(5, ticketPrice);
				ps.setString(6, ticketFlag);
				
				ps.addBatch();
			}
			
			ps.executeBatch();
			
			// 提交事务
			con.commit();
			// 还原当前自动提交模式
			con.setAutoCommit(autoCommit);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
	}
	
	/**
	 * 根据成员ID统计电影票信息
	 * @param map
	 */
	public void queryTicketCountByMemberId(Map<String, String> map) {
		
		String memberId = map.get("memberId");
		
		String sql = "select count(1) from ticket where ticket_member=?";
		
		try {
			con = DBUtil.getConnection();
			
			ps = con.prepareStatement(sql);
			ps.setString(1, memberId);
			
			rs = ps.executeQuery();
			
			if(rs.next()) {
				map.put("dataCount", rs.getInt(1) + "");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
	}
	
	/**
	 * 根据成员ID查询电影票信息
	 * @param condMap
	 * @return
	 */
	public List<Map<String, String>> queryTicketByMemberId(Map<String, String> condMap) {
		
		String memberId = condMap.get("memberId");
		int limit = Integer.parseInt(condMap.get("limit"));
		int pos = Integer.parseInt(condMap.get("pos"));
		
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		
		StringBuffer sql = new StringBuffer();
		sql.append("select t1.ticket_id,t1.ticket_play,t1.ticket_member,t1.ticket_code,t1.ticket_seat,t1.ticket_price,")
		   .append("convert(varchar(16),t1.ticket_date,20) ticket_date,convert(varchar(16),t2.play_time,20) play_time1,")
		   .append("convert(varchar(19),dateadd(hour,-2,t2.play_time),20) play_time2,t3.movie_name,t4.edition_name,t1.ticket_flag ")
		   .append("from ticket t1,play t2,movie t3,edition t4 ")
		   .append("where t1.ticket_play=t2.play_id and t2.play_movie=t3.movie_id and t3.movie_edition=t4.edition_id ")
		   .append("and t1.ticket_member=? order by t1.ticket_id");
		
		String _sql = DataUtil.getPagingSqlBySQLServer2005High(sql.toString(), limit, pos);
		
		try {
			con = DBUtil.getConnection();
			
			ps = con.prepareStatement(_sql);
			ps.setString(1, memberId);
			
			rs = ps.executeQuery();
			
			while(rs.next()) {
				String ticketId = rs.getString("ticket_id");
				String ticketPlay = rs.getString("ticket_play");
				String ticketMember = rs.getString("ticket_member");
				String ticketCode = rs.getString("ticket_code");
				String ticketSeat = rs.getString("ticket_seat");
				String ticketPrice = rs.getString("ticket_price");
				String ticketDate = rs.getString("ticket_date");
				String playTime1 = rs.getString("play_time1");
				String playTime2 = rs.getString("play_time2");
				String movieName = rs.getString("movie_name");
				String editionName = rs.getString("edition_name");
				String ticketFlag = rs.getString("ticket_flag");
				
				Map<String, String> map = new HashMap<String, String>();
				map.put("ticketId", ticketId);
				map.put("ticketPlay", ticketPlay);
				map.put("ticketMember", ticketMember);
				map.put("ticketCode", ticketCode);
				map.put("ticketSeat", ticketSeat);
				map.put("ticketPrice", ticketPrice);
				map.put("ticketDate", ticketDate);
				map.put("playTime1", playTime1);
				map.put("playTime2", playTime2);
				map.put("movieName", movieName);
				map.put("editionName", editionName);
				map.put("ticketFlag", ticketFlag);
				
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
		
		return list;
	}
	/**
	 * 查询用户头像
	 */
	public Map<String,String> queryMemberPhoto(String memberId) {
		
		String sql = "select * from member where member_id=?";
		Map<String,String> map = new HashMap<String,String>();
		try {
			con = DBUtil.getConnection();
			
			ps = con.prepareStatement(sql);
			ps.setString(1, memberId);
			rs = ps.executeQuery();
			if(rs.next()){
				String adressPhoto = rs.getString("member_photo");
				String memberPwd = rs.getString("member_pwd");
				String money = rs.getString("member_money");
				String email = rs.getString("member_email");
				String phone = rs.getString("member_phone");
				String name = rs.getString("member_name");
				String gender = rs.getString("member_gender");
				
				
				map.put("adressPhoto", adressPhoto);
				map.put("memberPwd", memberPwd);
				map.put("ID", memberId);
				map.put("money", money);
				map.put("email", email);
				map.put("phone", phone);
				map.put("name", name);				
				map.put("gender", gender);	
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
		return map;
	}
	
	/**
	 * 用户修改密码
	 */
	public boolean updateMemberPwd(String memberId,String newPwd) {
		
		String sql = "update member set member_pwd=?  where member_id=?";
		try {
			con = DBUtil.getConnection();
			
			ps = con.prepareStatement(sql);
			ps.setString(1, newPwd);
			ps.setString(2, memberId);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
		return true;
	}
	
	/**
	 * 用户修改资料
	 */
	public boolean updateMemberData(Map<String,String> map) {
		
		String sql = "update member set member_name=?,member_phone=?,member_gender=?  where member_id=?";
		try {
			con = DBUtil.getConnection();
			
			ps = con.prepareStatement(sql);
			ps.setString(1, map.get("name"));
			ps.setString(2, map.get("phone"));
			ps.setString(3, map.get("gender"));
			ps.setString(4, map.get("memberId"));
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
		return true;
	}
	
	/**
	 * 用户修改头像
	 */
	public boolean updatePhoto(String id,String Photo) {
		
		String sql = "update member set member_photo=? where member_id=?";
		try {
			con = DBUtil.getConnection();
			
			ps = con.prepareStatement(sql);
			ps.setString(1, Photo);
			ps.setString(2, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
		return true;
	}
	
	
	
	public void updateTicketFlagByTicketId(String ticketId, String ticketFlag) {
		
		String sql = "update ticket set ticket_flag=? where ticket_id=?";
		
		try {
			con = DBUtil.getConnection();
			
			ps = con.prepareStatement(sql);
			ps.setString(1, ticketFlag);
			ps.setString(2, ticketId);
			
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
	}
	
	public void updateAddMemberMoneyByMemberId(String memberId, String ticketPrice) {
		
		String sql = "update member set member_money+=? where member_id=?";
		
		try {
			con = DBUtil.getConnection();
			
			ps = con.prepareStatement(sql);
			ps.setString(1, ticketPrice);
			ps.setString(2, memberId);
			
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
	}
}